package cn.mogugo.comm;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import org.springframework.util.Assert;


public class BaseDao extends HibernateDaoSupport{
	private static final Log log = LogFactory.getLog(BaseDao.class);


	
	public int executeSql(String sql , List value) throws Exception{
		Session sess = null;
		Connection con = null;
		PreparedStatement stmt = null;
		int excuteNum = 0;
		try{
			sess = getHibernateTemplate().getSessionFactory().openSession();
			Transaction tx = sess.beginTransaction();
			con = sess.connection();
			stmt = con.prepareStatement(sql);
			for(int i = 0 ; i < value.size() ; i ++ ){
				Object obj = value.get(i);
				if(obj instanceof Integer){
					stmt.setInt(i+1, (Integer)obj);
				}
				if(obj instanceof String){
					stmt.setString(i+1, (String)obj);
				}
			}
			excuteNum = stmt.executeUpdate();
			tx.commit();
		}catch(Exception e){
			try{
				stmt.close();
				con.close();
				sess.close();
			}catch(Exception e1){
				e1.printStackTrace();
			}
		}finally{
			try{
				stmt.close();
				con.close();
				sess.close();
			}catch(Exception e1){
				e1.printStackTrace();
			}
		}
		return excuteNum;
	}
	
	
	/**
	 * 
	 * @param sql
	 * @param value
	 * @return
	 */
	public List getBySql(String sql , List value){
		Session sess = null;
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List listOfRows = new ArrayList();
		try{
			sess = getHibernateTemplate().getSessionFactory().openSession();
			Transaction tx = sess.beginTransaction();
			con = sess.connection();
			
			stmt = con.prepareStatement(sql);
			if(value != null && !value.isEmpty()){
				for(int i = 0 ; i < value.size() ; i ++ ){
					Object obj = value.get(i);
					if(obj instanceof Integer){
						stmt.setInt(i+1, (Integer)obj);
					}
					if(obj instanceof String){
						stmt.setString(i+1, (String)obj);
					}
				}
			}

			rs = stmt.executeQuery();
			
			
			ResultSetMetaData md = rs.getMetaData();
			int num = md.getColumnCount();
			while (rs.next()) {
				Map mapOfColValues = new HashMap(num);
				for (int i = 1; i <= num; i++) {
					//System.out.println(md.getColumnName(i) + "----------------------" + rs.getObject(i));
					mapOfColValues.put(md.getColumnName(i), rs.getObject(i));
				}
				listOfRows.add(mapOfColValues);
			}

			tx.commit();
		}catch(Exception e){
			e.printStackTrace();
			try{
				rs.close();
				stmt.close();
				con.close();
				sess.close();
			}catch(Exception e1){
				e1.printStackTrace();
			}
		}finally{
			try{
				rs.close();
				stmt.close();
				con.close();
				sess.close();
			}catch(Exception e1){
				e1.printStackTrace();
			}
		}
		return listOfRows;
	}
	
	
	
    public PageSupport findPageBySql(final String sql , final Integer pageSize , Integer pageNo , List... value){
		Assert.hasText(sql);
		Assert.isTrue(pageNo >= 1, "pageNo should start from 1");
		
		if(pageNo == null || pageNo == 0){
			pageNo = 1;
		}
		Integer startNo = (pageNo - 1)*pageSize;
		Integer endNo = (pageNo) * pageSize;
		
		List listOfRows = new ArrayList();
		
		Session sess = null;
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		int totalNumber = 0;

		try{
			sess = getHibernateTemplate().getSessionFactory().openSession();
			Transaction tx = sess.beginTransaction();
			con = sess.connection();
			
			String countQueryString = "select count(*) " + removeSelect(removeOrders(sql));
			
			
			stmt = con.prepareStatement(countQueryString);
			if(value != null && value.length > 0){
				List listValue = value[0];
				for(int i = 0 ; i < listValue.size() ; i ++ ){
					Object obj = listValue.get(i);
					if(obj instanceof Integer){
						stmt.setInt(i+1, (Integer)obj);
					}
					if(obj instanceof String){
						stmt.setString(i+1, (String)obj);
					}
				}
			}

			rs = stmt.executeQuery();
			while(rs.next()){
				totalNumber = rs.getInt(1);
			}
			rs.close();
			

			String pageQuery = sql + "limit "+startNo+", "+endNo;
			
			
			stmt = con.prepareStatement(pageQuery);
			
			if(value != null && value.length > 0){
				List listValue = value[0];
				for(int i = 0 ; i < listValue.size() ; i ++ ){
					Object obj = listValue.get(i);
					if(obj instanceof Integer){
						stmt.setInt(i+1, (Integer)obj);
					}
					if(obj instanceof String){
						stmt.setString(i+1, (String)obj);
					}
				}
			}

			rs = stmt.executeQuery();
			
			ResultSetMetaData md = rs.getMetaData();
			int num = md.getColumnCount();
			while (rs.next()) {
				Map mapOfColValues = new HashMap(num);
				for (int i = 1; i <= num; i++) {
					//System.out.println(md.getColumnName(i) + "----------------------" + rs.getObject(i));
					mapOfColValues.put(md.getColumnName(i), rs.getObject(i));
				}
				listOfRows.add(mapOfColValues);
			}

			tx.commit();
			
		}catch(Exception e){
			//System.out.println("-----------catch-----------");
			e.printStackTrace();
			try{
				rs.close();
				stmt.close();
				con.close();
				sess.close();
			}catch(Exception e1){
				e1.printStackTrace();
			}
		}finally{
			//System.out.println("-----------finally-----------");
			try{
				rs.close();
				stmt.close();
				con.close();
				sess.close();
			}catch(Exception e1){
				e1.printStackTrace();
			}
		}
		
		PageSupport p = new PageSupport(listOfRows, totalNumber, pageSize, pageNo);
    	return p;
    }
	
	/**
	 * 公共的hql查询方法
	 * 2009－02－09 add
	 * @param hql
	 * @param params
	 * @return
	 */
	public List getByHQL(String hql , Map... params){
		Query q = getSession().createQuery(hql);
		if(params != null && params.length > 0){
			setParameters(q, params[0]);
		}
		return q.list();
	}
	
	
	public int excuteByHql(String hql , Map... params){
		Query q = getSession().createQuery(hql);
		if(params != null  && params.length > 0){
			setParameters(q, params[0]);
		}
		return q.executeUpdate();
	}
	/**
	 * 匹配参数
	 * 2009-02-09 add
	 * @param query
	 * @param params
	 */
	public static void setParameters(Query query, Map params) {
		if(params != null){
			Set keys = params.keySet();
			for (Object key : keys) {
				String paramName = (String) key;
				Object value = params.get(key);
				if (value instanceof Collection) {
					query.setParameterList(paramName, (Collection) value);
				}else if (value instanceof Object[]) {
					query.setParameterList(paramName, (Object[]) value);
				}else if(value instanceof Integer){
					query.setInteger(paramName, (Integer)value);
				}else{
					query.setParameter(paramName, value);
				}
			}
		}
	}
	
	
	/**
	 * 去除hql的orderby 子句，用于pagedQuery.
	 *
	 * @see #pagedQuery(String,int,int,Object[])
	 */
	protected static String removeOrders(String hql) {
		Assert.hasText(hql);
		Pattern p = Pattern.compile("order\\s*by[\\w|\\W|\\s|\\S]*", Pattern.CASE_INSENSITIVE);
		Matcher m = p.matcher(hql);
		StringBuffer sb = new StringBuffer();
		while (m.find()) {
			m.appendReplacement(sb, "");
		}
		m.appendTail(sb);
		return sb.toString();
	}
	/**
	 * 去除hql的select 子句，未考虑union的情况,用于pagedQuery.
	 *
	 * @see #pagedQuery(String,int,int,Object[])
	 */
	protected static String removeSelect(String hql) {
		Assert.hasText(hql);
		int beginPos = hql.toLowerCase().indexOf("from");
		Assert.isTrue(beginPos != -1, " hql : " + hql + " must has a keyword 'from'");
		return hql.substring(beginPos);
	}
	/**
	 * 创建Query对象. 对于需要first,max,fetchsize,cache,cacheRegion等诸多设置的函数,可以在返回Query后自行设置.
	 * 留意可以连续设置,如下：
	 * <pre>
	 * dao.getQuery(hql).setMaxResult(100).setCacheable(true).list(); 
	 * </pre>
	 * 调用方式如下：
	 * <pre>
	 *        dao.createQuery(hql)
	 *        dao.createQuery(hql,arg0);
	 *        dao.createQuery(hql,arg0,arg1);
	 *        dao.createQuery(hql,new Object[arg0,arg1,arg2])
	 * </pre>
	 *
	 * @param values 可变参数.
	 */
	protected Query createQuery(String hql, Object... values) {
		Assert.hasText(hql);
		Query query = getSession().createQuery(hql);
		for (int i = 0; i < values.length; i++) {
			query.setParameter(i, values[i]);			
		}
		return query;
	}
	
	
	
	/**
	 * 根据id获取对象
	 * @param clazz
	 * @param id
	 * @return
	 */
    public Object get(Class clazz , Serializable id) {
//    	Object obj;
//    	try{
//    		obj = getHibernateTemplate().load(clazz, id);
//    	}catch(org.hibernate.ObjectNotFoundException e){
//    		obj = null;
//    	}
//    	return obj;
    	Object obj = getHibernateTemplate().get(clazz, id);
    	return obj;
    }
    
    
    public Object getUniqueBy(Class clazz , String propertyName, Object value) {
    	return getSession().createCriteria(clazz).add(Restrictions.eq(propertyName, value)).uniqueResult();
    }
    
    public Object getUniqueBy(String hql , Map... params) {
		Query q = getSession().createQuery(hql);
		if(params != null && params.length > 0){
			setParameters(q, params[0]);
		}
		return q.uniqueResult();
    }
    
    /**
     * 取所有的记录，并按照规则排序
     * @param clazz
     * @param orderBy
     * @param isAsc
     * @return
     */
    public List getAll(Class clazz , String orderBy, Boolean isAsc , Boolean... isUsed){
    	DetachedCriteria dc = DetachedCriteria.forClass(clazz);
    	if(isUsed != null){
    		Criterion c;
    		if(isUsed[0]){
    			c = Restrictions.eq("is_used", CC.YES);
    		}else{
    			c = Restrictions.eq("is_used", CC.NO);
    		}
    		dc.add(c);
    	}    	
    	if(isAsc != null){
    		Order order;
            if (isAsc.booleanValue()) {
            	order = Order.asc(orderBy);
            } else {
            	order = Order.desc(orderBy);
            }
            dc.addOrder(order);
    	}
    	return getHibernateTemplate().findByCriteria(dc);
    }
    
    
    /**
     * 取所有的记录，并按照规则排序，默认按照  sort  排序
     * @param clazz
     * @param isAsc
     * @return
     */
    public List getAll(Class clazz , Boolean isAsc){
        return getAll(clazz , "sort" , isAsc , null);
    }
    
    /**
     * 取所有的记录，无排序
     * @param clazz
     * @return
     */
    public List getAll(Class clazz){
        return getAll(clazz , null , null , null);
    }
    
    
    /**
     * 取所有的(有效/无效)记录，并按照规则排序，默认按照  sort  排序，是否有效按照is_used判断。
     * @param clazz
     * @param isAsc
     * @param isUsed
     * @return
     */
    public List getAll(Class clazz , Boolean isAsc , Boolean isUsed){
    	return getAll(clazz , "sort" , isAsc , isUsed);
    }
    
    /**
     * 取所有的记录，并按照规则排序
     * @param clazz
     * @param orderBy
     * @param isAsc
     * @return
     */
    public List getAll(Class clazz , String[] orderBy, boolean[] isAsc){
    	DetachedCriteria dc = DetachedCriteria.forClass(clazz);
    	int length = orderBy.length;
    	for(int i = 0 ; i < length ; i ++ ){
            Order order;
            if (isAsc[i]) {
            	order = Order.asc(orderBy[i]);
            } else {
            	order = Order.desc(orderBy[i]);
            }
            dc.addOrder(order);
    	}
        return getHibernateTemplate().findByCriteria(dc);
    }
    
    public List getAllById(Class clazz , Integer[] value){
    	List listValue = new ArrayList();
    	for(Integer id : value){
    		listValue.add(getHibernateTemplate().get(clazz, id));
    	}
    	return listValue;
    }
    
    //----------------------含日志处理的方法--------------------
    
    
    public String saveOrUpdate(Object obj){
    	return saveOrUpdateLog(obj , true);
    }
    
    /**
     * 保存对象，同时记录日志
     * @param obj
     */
    public String saveOrUpdateLog(Object obj){
    	return saveOrUpdateLog(obj , true);
    }
    
    /**
     * 保存对象，同时记录日志
     * @param obj
     * @param isLog 
     */
    public String saveOrUpdateLog(Object obj , boolean isLog){
    	String strRtn = "";
    	try{
    		getHibernateTemplate().saveOrUpdate(obj);
    		strRtn += "成功";
    	}catch(Exception e){
			strRtn += "失败";
    	}
    	return strRtn;
    }
    
    public void saveOrUpdateFlush(Object obj){
    	Session session = this.getHibernateTemplate().getSessionFactory().getCurrentSession();
    	session.save(obj);
    	session.flush();
    }
    
    public void saveOrUpdateFlush(Object[] obj){
    	Session session = this.getHibernateTemplate().getSessionFactory().getCurrentSession();
    	for(int i = 0 ; i < obj.length ; i ++ ){
    		session.save(obj[i]);
    	}
    	session.flush();
    }
    
    public void saveOrUpdateFlush(List obj){
    	Session session = this.getHibernateTemplate().getSessionFactory().getCurrentSession();
    	for(int i = 0 ; i < obj.size() ; i ++ ){
    		session.save(obj.get(i));
    	}
    	session.flush();
    }
    
    /**
     * 根据id删除对象，同时记录日志
     * @param arrayMenuId
     * @return
     */
	public String deleteObjectByIdLog(Class clazz , Integer[] arrayId){
		return deleteObjectByIdLog(clazz , arrayId , true);
	}
	
	public String deleteObjectByIdLog(Class clazz , Integer id){
		Integer[] arrayId = {id};
		return deleteObjectByIdLog(clazz , arrayId , true);
	}
	
	public String deleteObjectByIdLog(Class clazz , Integer[] arrayId , boolean isLog){
		List list = getAllById(clazz , arrayId);
		return deleteObjectInCollectionLog(list , isLog);
	}
	
	
	/**
	 * 直接删除对象集合，同时记录日志
	 * @param listObject
	 * @return
	 */
	public String deleteObjectInCollectionLog(Collection c){
        return deleteObjectInCollectionLog(c , true);
	}
	
	/**
	 * 直接删除对象集合
	 * @param c
	 * @param isLog
	 * @return
	 */
	public String deleteObjectInCollectionLog(Collection c  , boolean isLog){
		String strRtn = null;
        try {
        	getHibernateTemplate().deleteAll(c);
        	strRtn = "删除成功";
        } catch (RuntimeException e) {
        	strRtn = "删除失败";
        	e.printStackTrace();
        }
        return strRtn;
	}
	
	/**
	 * 直接删除对象，同时记录日志
	 * @param obj
	 * @return
	 */
	public String deleteObjectLog(Object obj){
        return deleteObjectLog(obj , true);
	}
	
	
	/**
	 * 直接删除对象
	 * @param obj
	 * @param isLog
	 * @return
	 */
	public String deleteObjectLog(Object obj , boolean isLog){
		String strRtn = null;
        try {
        	getHibernateTemplate().delete(obj);
        	strRtn = "删除成功";
        } catch (RuntimeException e) {
        	strRtn = "删除失败";
        }
        return strRtn;
	}
	
	
    //----------------------含日志处理的方法--------------------
	
	/**
	 * 针对前台action调用的分页查询方法的包装
	 * @param clazz
	 * @param orderBy
	 * @param isAsc
	 * @return
	 */
    public PageSupport findPage(Class clazz , String orderBy, boolean isAsc , Integer pageNo){
        Order order;
        if (isAsc) {
        	order = Order.asc(orderBy);
        } else {
        	order = Order.desc(orderBy);
        }
        DetachedCriteria dc = DetachedCriteria.forClass(clazz).addOrder(order);
        return findPageByCriteria(dc , CC.PAGE_SIZE , pageNo);
    }
    
	/**
	 * 针对前台action调用的分页查询方法的包装
	 * 简写方式，默认 orderBy="sort" , isAsc="true"
	 * @param clazz
	 * @return
	 */
    public PageSupport findPage(Class clazz , Integer pageNo){
    	return findPage(clazz , "sort" , true , pageNo);
    }
    
    
	/**
	 * 分页查询
	 * @param detachedCriteria
	 * @param pageSize
	 * @param startIndex
	 * @return
	 */
    public PageSupport findPageByCriteria(final DetachedCriteria detachedCriteria, final int pageSize, final int pageNo) {
        return (PageSupport) getHibernateTemplate().execute(new HibernateCallback() {    
            public Object doInHibernate(Session session) throws HibernateException {    
                 Criteria criteria = detachedCriteria.getExecutableCriteria(session);    
                int totalCount = ((Integer) criteria.setProjection(Projections.rowCount()).uniqueResult()).intValue();    
                 criteria.setProjection(null);
                 int startIndex = (pageNo-1)*pageSize;
                 List items = criteria.setFirstResult(startIndex).setMaxResults(pageSize).list();
                 PageSupport ps = new PageSupport(items, totalCount, pageSize, pageNo);    
                return ps;    
             }    
         }, true);
     }
    
    
    /**
     * hql分页检索
     * @param hql hql查询语句
     * @param pageSize 每页记录条数
     * @param pageNo 当前页数
     * @param values 参数值
     * @return
     */
    public PageSupport findPageByHql(final String hql, final int pageSize, final int pageNo , Map values) {
		Assert.hasText(hql);
		Assert.isTrue(pageNo >= 1, "pageNo should start from 1");
		
		// Count查询
		String countQueryString = " select count (*) " + removeSelect(removeOrders(hql));
		
		Query query1 = createQuery(countQueryString);
		setParameters(query1 , values);
		Long totalCount = (Long)query1.list().get(0);
		
		// 实际查询返回分页对象
		int startIndex = (pageNo-1)*pageSize;
		Query query = createQuery(hql);
		setParameters(query , values);
		List list = query.setFirstResult(startIndex).setMaxResults(pageSize).list();

		PageSupport p = new PageSupport(list, totalCount.intValue(), pageSize, pageNo);
    	return p;

     }
    
    
    
    /**
     * hql分页检索
     * @param hql hql查询语句
     * @param pageSize 每页记录条数
     * @param pageNo 当前页数
     * @param values 参数值
     * @return
     */
    public List findPageByHqlWithStartIndex(String hql, int pageSize, int startIndex , Map values) {
		Assert.hasText(hql);
		
		// Count查询
		String countQueryString = " select count (*) " + removeSelect(removeOrders(hql));
		
		Query query1 = createQuery(countQueryString);
		setParameters(query1 , values);
		Long totalCount = (Long)query1.list().get(0);
		if(totalCount.intValue() < pageSize + startIndex){
			pageSize = totalCount.intValue() - startIndex;
		}
		
		Query query = createQuery(hql);
		setParameters(query , values);
		List list = query.setFirstResult(startIndex).setMaxResults(pageSize).list();

    	return list;

     }
    
    
    
    public PageSupport findPageByHql(final String hql, final int pageNo , Map values) {
		return this.findPageByHql(hql, CC.PAGE_SIZE, pageNo, values);
     }
    
     public PageSupport findPageByHql(final String hql , final int pageNo){
    	 return this.findPageByHql(hql, CC.PAGE_SIZE, pageNo, null);
     }
    
    
    /**
     * hql分页检索
     * @param hql hql查询语句
     * @param pageSize 每页记录条数
     * @param pageNo 当前页数
     * @param totalCount 总记录数
     * @param values 参数值
     * @return
     */
    public PageSupport findPageByHql(String hql, Integer pageSize, Integer pageNo , Integer totalCount , Map values) {
		Assert.hasText(hql);
		Assert.isTrue(pageNo >= 1, "pageNo should start from 1");
				
		// 实际查询返回分页对象
		if(pageSize == null){
			pageSize = CC.PAGE_SIZE;
		}
		int startIndex = (pageNo-1)*pageSize;
		Query query = createQuery(hql);
		setParameters(query , values);
		List list = query.setFirstResult(startIndex).setMaxResults(pageSize).list();

		PageSupport p = new PageSupport(list, totalCount, pageSize, pageNo);
    	return p;
     }
    
    public int findTotalCountByHql(final String hql , Map values){
    	Query query = createQuery(hql);
		setParameters(query , values);
		Long totalCount = (Long)query.list().get(0);
		return totalCount.intValue();
		
    }


}
